python导出excel成lua表
1，需安装python 2.7.2
2，程序用到第三方库xlrd，需要另行安装
tool\xls2table.py:
#! /usr/bin/env python
# -*- coding: cp936 -*-
# convert excel xls file to lua script with table data
# date/time values formatted as string, int values formatted as int
# depend on xlrd module
# fanlix 2008.1.25
# Modify:
# 2008.3.18 merged-cell handles: copy data from top-left cell to other cells
import xlrd
import os.path
FLOAT_FORMAT = "%.8f"
SCRIPT_HEAD = '''''-- excel xlstable format (sparse 3d matrix)
--{ [sheet1] = { [row1] = {
[col1] = value, [col2] = value, ...},
-- [row5] = { [col3] = value, }, },
-- [sheet2] = { [row9] = { [col9] = value, }},
--}
-- nameindex table
--{ [sheet,row,col name] = index, .. }
'''
SCRIPT_END = '''''-- functions for xlstable read
local __getcell = function (t, a,b,c) return t[a][b][/c]
end
function GetCell(sheetx, rowx, colx)
rst, v = pcall(__getcell, xlstable, sheetx, rowx, colx)
if rst then return v
else return nil
end
end
function GetCellBySheetName(sheet, rowx, colx)
return GetCell(sheetname[sheet], rowx, colx)
end
'''
def gen_table(filename):
if not os.path.isfile(filename):
raise NameError, "%s is not a valid filename" % filename
book = xlrd.open_workbook(filename,formatting_info=True)
luaT = {}
luaN = {}
sidx = 0
for sheet in book.sheets():
sdict = {}
ridx = 0
for ridx in xrange(sheet.nrows):
rdict = {}
for cidx in xrange(sheet.ncols):
value = sheet.cell_value(ridx, cidx)
vtype = sheet.cell_type(ridx, cidx)
v = format_value(value, vtype, book)
#print sidx, ridx, cidx, value, vtype, v
if v is not None and value != "":
rdict[cidx] = v
if rdict: sdict[ridx] = rdict
if sdict: luaT[sidx] = sdict
# handle merged-cell
for crange in sheet.merged_cells:
rlo, rhi, clo, chi = crange
try:
v = sdict[rlo][clo]
except KeyError:
# empty cell
continue
if v is None or v == "": continue
for ridx in xrange(rlo, rhi):
if ridx not in sdict:
sdict[ridx] = {}
for cidx in xrange(clo, chi):
sdict[ridx][cidx] = v
name = sheet.name
luaN[name] = sidx
luaT[sidx] = sdict
sidx += 1
#print "--------- luaT:", luaT
return luaT, luaN
def format_value(value, vtype, book):
''''' format excel cell value, int?date?
'''
if vtype == 2:
if value == int(value):
value = int(value)
elif type(value) == float :
pass
elif vtype == 3:
datetuple = xlrd.xldate_as_tuple(value, book.datemode)
# time only no date component
if datetuple[0] == 0 and datetuple[1] == 0 and datetuple[2] == 0:
value = "%02d:%02d:%02d" % datetuple[3:]
# date only, no time
elif datetuple[3] == 0 and datetuple[4] == 0 and datetuple[5] == 0:
value = "%04d/%02d/%02d" % datetuple[:3]
else: # full date
value = "%04d/%02d/%02d %02d:%02d:%02d" % datetuple
return value
def format_output(v):
s = ("%s"%(v)).encode("gbk")
if s[-1] == "]":
s = "%s "%(s)
return s
def write_table(luaT, luaN, outfile = '-', withfunc = True):
''''' lua table key index starts from 1
'''
if outfile and outfile != '-':
outfp = open(outfile, 'w')
outfp.write(SCRIPT_HEAD)
else:
import StringIO
outfp = StringIO.StringIO()
outfp.write("sheetname = {\n")
for k,v in luaN.iteritems():
outfp.write("[\"%s\"] = %d,\n"%(format_output(k), v + 1))
outfp.write("};\n\n")
outfp.write("sheetindex = {\n")
for k, v in luaN.iteritems():
outfp.write("[%d] = \"%s\",\n" %(v+1, format_output(k)))
outfp.write("};\n\n")
outfp.write("xlstable = {\n")
for sidx, sheet in luaT.iteritems():
outfp.write("[%d] = {\n"%(sidx + 1))
for rowidx, row in sheet.iteritems():
outfp.write("\t[%d] = {\n"%(rowidx + 1))
for colidx, col in row.iteritems():
try:
if type(col) is int: s = "%d"%(col)
elif type(col) is float: s = FLOAT_FORMAT%(col)
else : s = "[[%s]]"%(format_output(col))
outfp.write("\t\t[%d] = %s,\n"%(colidx + 1, s))
except Exception, e:
raise Exception("Write Table error (%s,%s,%s) : %s"%(sidx+1,rowidx+1,colidx+1,str(e)))
outfp.write("\t},\n")
outfp.write("},\n")
outfp.write("};\n\n")
if withfunc: outfp.write(SCRIPT_END)
outfp.write("\n__XLS_END = true\n")
if not outfile or outfile == '-':
outfp.seek(0)
print outfp.read()
outfp.close()
def main():
import sys
if len(sys.argv)
sys.exit('''''usage: filename outputfile('-' for stdout by default)''')
filename = sys.argv[1]
try: output = sys.argv[2]
except: output = '-'
t, n = gen_table(filename)
write_table(t, n, output, withfunc = True)
if __name__=="__main__":
main()
tool/test.lua:
function ParseTable(table)
local CodeString = "--玩家道具\n local autoTable = {\n"
for line, Data in ipairs(table[1]) do --只读第一页
--第一行不读
if line > 1 then
CodeString = CodeString .. string.format("\t[%d] = {\n", Data[1])
CodeString = CodeString .. string.format("\t\ttype = 'voucher',\n")
CodeString = CodeString .. string.format("\t\tareaType = 'other',\n")
CodeString = CodeString .. string.format("\t\tname = '%s',\n", Data[3])
CodeString = CodeString .. string.format("\t\tdescribe = '%s',\n", Data[4])
if Data[5] then
CodeString = CodeString .. string.format("\t\teffect = '%s',\n", Data[5])
end
CodeString = CodeString .. "\t},\n"
end
end
CodeString = CodeString .. "}\n"
CodeString = CodeString .. "function GetTable()\n\treturn autoTable\nend\n"
return CodeString
end
function SaveCode(file, Code)
local fd = io.open(file, "w")
assert(fd)
fd:write(Code)
fd:close()
end
function gen()
local excelfile = "excel/ping-zheng.xls"
local outfile = "autocode/voucher.lua"
local cmd = string.format([python 'tool/xls2table.py' '%s'], excelfile)
local fd = io.popen(cmd)
local loadxls = loadstring(fd:read("*a"))
assert(loadxls)
loadxls()
assert(__XLS_END)
fd:close()
local Code = ParseTable(xlstable)
SaveCode(outfile, Code)
end
gen()
